package org.rooster.dbmanager.datasource;

import org.rooster.schedule.*;

import java.io.*;
import java.net.UnknownHostException;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Created by IntelliJ IDEA.
 * User: manorama
 * Date: 7/29/12
 * Time: 12:37 PM
 * To change this template use File | Settings | File Templates.
 */
public class MySQLDataSource implements DataSource {

    private String dbHost;
    private String dbName;
    private String dbUsername;
    private String dbPassword;
    private String driver;
    private Connection connection;
    private String RESOURCE_REQUIREMENT_TABLE = " rooster_resource_requirement";
    private String SCHEDULE_TABLE = "rooster_schedule";
    private String RESOURCE_TABLE = "rooster_resource";
    private String RESOURCE_CAPABILITY_TABLE = "rooster_resource_capability";
    private String MANAGER_PREFERENCE_TABLE = "rooster_manager_preferences";
    private String RESOURCE_PREFERENCE_TABLE = "rooster_resource_preferences";
    private String TASK_TABLE = "rooster_task";
    private String TIME_SLOT_TABLE = "rooster_time_slot";
    private String RESOURCE_TYPE_TABLE = "rooster_resource_type";

    /**
     * 
     */
    public MySQLDataSource() {
        initializeDatabaseConfiguration();
    }

    private void initializeDatabaseConfiguration() {

        loadProperties();
    }

    /**
     * Get the required configuration details from the properties file.
     */
    public void loadProperties() {

        Properties properties = new Properties();
        InputStream inputStream = MySQLDataSource.class.getResourceAsStream("/database.properties");
        try {
            properties.load(inputStream);
            dbHost = properties.getProperty("db.host");
            dbName = properties.getProperty("db.name");
            dbUsername = properties.getProperty("db.username");
            dbPassword = properties.getProperty("db.password");
            driver = properties.getProperty("db.driver");

        } catch (IOException e) {
            Logger.getLogger(MySQLDataSource.class.getName()).log(Level.SEVERE,
                    "Error occured while loading application properties file", e);
        } finally {
            try {
                inputStream.close();
            } catch (IOException ignored) {
            }
        }
    }

    /**
     * 
     * @return
     * @throws UnknownHostException
     * @throws SQLException
     */
    protected Connection getDatabaseConnection() throws UnknownHostException, SQLException {

        if (connection == null) {

            //TODO: Have a separate database connection class for mysql datasource
            DatabaseConnection databaseConnection = new DatabaseConnection();
            databaseConnection.setDbName(dbName);
            databaseConnection.setUrl(dbHost);
            databaseConnection.setDriver(driver);
            databaseConnection.setUserName(dbUsername);
            databaseConnection.setPassword(dbPassword);

            try {
                Class.forName(databaseConnection.getDriver());
            } catch (java.lang.ClassNotFoundException e) {
                System.err.println(e);
            }
            connection = (Connection) DriverManager.getConnection(databaseConnection.getUrl() + databaseConnection.getDbName(), databaseConnection.getUserName(), databaseConnection.getPassword());
        }
        return connection;
    }

    @Override
    public int insertResourceRequirement(ResourceRequirement resourceRequirement) throws UnknownHostException, SQLException {


        String addUserQuery = "insert into " + RESOURCE_REQUIREMENT_TABLE + " values(?, ? ,?, ?)";
        PreparedStatement addUserStmt = getDatabaseConnection().prepareStatement(addUserQuery);
        addUserStmt.setInt(1, resourceRequirement.getTaskId());                       //Task
        addUserStmt.setInt(2, resourceRequirement.getTimeSlotId());                   //Time Slot
        addUserStmt.setInt(3, resourceRequirement.getResourceTypeId());   //Resource Type
        addUserStmt.setInt(4, resourceRequirement.getNoOfResourcesRequired());        //Required no of resources
        return addUserStmt.executeUpdate();

    }

    @Override
    public Set retrieveResourceRequirementsForSchedule() throws UnknownHostException, SQLException {

        Set<ResourceRequirement> resourceRequirementSet = new HashSet<ResourceRequirement>();
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM " + RESOURCE_REQUIREMENT_TABLE);

        while (resultSet.next()) {
            ResourceRequirement resourceRequirement = new ResourceRequirement();
            resourceRequirement.setTaskId(resultSet.getInt("task_id"));
            resourceRequirement.setTimeSlotId(resultSet.getInt("time_slot_id"));
            resourceRequirement.setResourceTypeId(resultSet.getInt("resource_type_id"));
            resourceRequirement.setNoOfResourcesRequired(resultSet.getInt("required_number"));

            resourceRequirementSet.add(resourceRequirement);
        }
        return resourceRequirementSet;
    }

    @Override
    public List getTaskRequirement(int taskNo) throws UnknownHostException, SQLException {

        List<ResourceRequirement> resourceRequirementList = new ArrayList<ResourceRequirement>();
        String getRequirementQuery = "SELECT * FROM " + RESOURCE_REQUIREMENT_TABLE + " WHERE task_id = ?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);
        preparedStatement.setInt(1, taskNo);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            ResourceRequirement resourceRequirement = new ResourceRequirement();
            resourceRequirement.setTaskId(resultSet.getInt("task_id"));
            resourceRequirement.setTimeSlotId(resultSet.getInt("time_slot_id"));
            resourceRequirement.setResourceTypeId(resultSet.getInt("resource_type_id"));
            resourceRequirement.setNoOfResourcesRequired(resultSet.getInt("required_number"));
            resourceRequirementList.add(resourceRequirement);
        }
        return resourceRequirementList;
    }

    @Override
    public Set getScheduleResourceAllocations() throws UnknownHostException, SQLException {

        Set<ResourceAllocation> resourceAllocationSet = new HashSet<ResourceAllocation>();
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM " + SCHEDULE_TABLE);

        while (resultSet.next()) {
            ResourceAllocation resourceAllocation = new ResourceAllocation();
            resourceAllocation.setTaskId(resultSet.getInt("task_id"));
            resourceAllocation.setTimeSlotId(resultSet.getInt("time_slot_id"));
            resourceAllocation.setResourceTypeId(resultSet.getInt("resource_type_id"));
            resourceAllocation.setResourceId(resultSet.getInt("resource_id"));

            resourceAllocationSet.add(resourceAllocation);
        }
        return resourceAllocationSet;
    }

    @Override
    public int insertResourceAllocation(ResourceAllocation resourceAllocation) throws UnknownHostException, SQLException {

        String addUserQuery = "insert into " + SCHEDULE_TABLE + " values(?, ? ,?, ?)";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(addUserQuery);
        statement.setInt(1, resourceAllocation.getTaskId());                       //Task
        statement.setInt(2, resourceAllocation.getTimeSlotId());                   //Time Slot
        statement.setInt(3, resourceAllocation.getResourceTypeId());   //Resource Type
        statement.setInt(4, resourceAllocation.getResourceId());                   //Resource id

        return statement.executeUpdate();
    }

    @Override
    public void clearScheduleTable() throws UnknownHostException, SQLException {
        Statement clearTableStmt = getDatabaseConnection().createStatement();
        clearTableStmt.executeQuery("TRUNCATE TABLE " + SCHEDULE_TABLE);
    }

    @Override
    public Resource getResourceRecord(int resourceID) throws UnknownHostException, SQLException {

        Resource resource = new Resource();
        String getRequirementQuery = "SELECT * FROM " + RESOURCE_TABLE + " WHERE resource_id = ?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);
        preparedStatement.setInt(1, resourceID);
        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.first()) {
            resource.setResourceId(resultSet.getInt("resource_id"));
            resource.setName(resultSet.getString("resource_name"));
            resource.setResourceTypeId(resultSet.getInt("resource_type_id"));
            if (resultSet.getInt("availability") == 1) {
                resource.setAvailability(true);
            }
        }
        return resource;
    }

    @Override
    public Set getAvailableResources() throws UnknownHostException, SQLException {
        Set<Resource> resourceSet = new HashSet<Resource>();
        String getRequirementQuery = "SELECT * FROM " + RESOURCE_TABLE + " WHERE availability=?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);
        preparedStatement.setInt(1, 1);   //1 for availability true
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            Resource resource = new Resource();
            resource.setResourceId(resultSet.getInt("resource_id"));
            resource.setName(resultSet.getString("resource_name"));
            resource.setResourceTypeId(resultSet.getInt("resource_type_id"));
            if (resultSet.getInt("availability") == 1) {
                resource.setAvailability(true);
            }
            resourceSet.add(resource);
        }
        return resourceSet;
    }

    // TODO: refactoring should be needed to make this method more generic to retreive any resources
    @Override
    public int getTotalResourceCount() throws UnknownHostException, SQLException {
        int count = 0;
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet res = statement.executeQuery("SELECT COUNT(*) FROM " + RESOURCE_TABLE);
        if (res.first()) {
            count = res.getInt(1);
        }
        return count;
    }

    //TODO: refactoring should be needed to make this method more generic to insert any resourceses capability
    @Override
    public int insertResourceCapability(ResourceCapability resourceCapability) throws SQLException, UnknownHostException {
        String query = "insert into " + RESOURCE_CAPABILITY_TABLE + " values(?, ? ,?)";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, resourceCapability.getResourceId());                   //Employee ID
        statement.setInt(2, resourceCapability.getTaskId());                       //Task
        statement.setInt(3, resourceCapability.getCapability());                   //Capability
        return statement.executeUpdate();
    }

    @Override
    public Set<ResourceCapability> getResourceCapabilities() throws SQLException, UnknownHostException {
        Set<ResourceCapability> resourceCapabilitySet = new HashSet<ResourceCapability>();
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM " + RESOURCE_CAPABILITY_TABLE);

        while (resultSet.next()) {
            ResourceCapability employeeCapability = new ResourceCapability();
            employeeCapability.setResourceId(resultSet.getInt("resource_id"));
            employeeCapability.setTaskId(resultSet.getInt("task_id"));
            employeeCapability.setCapability(resultSet.getInt("capability"));
            resourceCapabilitySet.add(employeeCapability);
        }
        return resourceCapabilitySet;
    }

    @Override
    public int insertManagerPreference(ManagerPreference managerPreference) throws SQLException, UnknownHostException {
        String query = "insert into " + MANAGER_PREFERENCE_TABLE + " values(?, ? ,?)";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, managerPreference.getTaskId());                   //Task ID
        statement.setInt(2, managerPreference.getResourceId());               //Employee ID
        statement.setInt(3, managerPreference.getPreference());               //Preference
        return statement.executeUpdate();
    }

    @Override
    public Set<ManagerPreference> getManagerPreferencesSet() throws SQLException, UnknownHostException {
        Set<ManagerPreference> managerPreferenceSet = new HashSet<ManagerPreference>();
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM " + MANAGER_PREFERENCE_TABLE);

        while (resultSet.next()) {
            ManagerPreference managerPreference = new ManagerPreference();
            managerPreference.setResourceId(resultSet.getInt("resource_id"));
            managerPreference.setTaskId(resultSet.getInt("task_id"));
            managerPreference.setPreference(resultSet.getInt("preference"));
            managerPreferenceSet.add(managerPreference);
        }
        return managerPreferenceSet;
    }

    @Override
    public int insertResourcePreferenceRecord(ResourcePreference resourcePreference) throws SQLException, UnknownHostException {
        String query = "insert into " + RESOURCE_PREFERENCE_TABLE + " values(?, ? ,?)";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, resourcePreference.getResourceId());                 //Employee ID
        statement.setInt(2, resourcePreference.getTaskId());                    //Task ID
        statement.setInt(3, resourcePreference.getPreference());               //Preference
        return statement.executeUpdate();
    }

    @Override
    public Set<ResourcePreference> getResourcePreferenceSet() throws SQLException, UnknownHostException {
        Set<ResourcePreference> resourcePreferenceSet = new HashSet<ResourcePreference>();
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM " + RESOURCE_PREFERENCE_TABLE);

        while (resultSet.next()) {
            ResourcePreference resourcePreference = new ResourcePreference();
            resourcePreference.setResourceId(resultSet.getInt("resource_id"));
            resourcePreference.setTaskId(resultSet.getInt("task_id"));
            resourcePreference.setPreference(resultSet.getInt("preference"));
            resourcePreferenceSet.add(resourcePreference);
        }
        return resourcePreferenceSet;
    }

    @Override
    public Set<Resource> getAllResources() throws SQLException, UnknownHostException {
        Set<Resource> resourceSet = new HashSet<Resource>();
        String getResourcesQuery = "SELECT * FROM " + RESOURCE_TABLE;
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(getResourcesQuery);

        while (resultSet.next()) {
            Resource resource = new Resource();
            resource.setResourceId(resultSet.getInt("resource_id"));
            resource.setName(resultSet.getString("resource_name"));
            resource.setResourceTypeId(resultSet.getInt("resource_type_id"));
            if (resultSet.getInt("availability") == 1) {
                resource.setAvailability(true);
            }
            resourceSet.add(resource);
        }
        return resourceSet;
    }

    @Override
    public int setResourceAvailability(int employeeId, int availability) throws SQLException, UnknownHostException {
        String query = "update " + RESOURCE_TABLE + " set availability=? where resource_id=?";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, availability);
        statement.setInt(2, employeeId);
        return statement.executeUpdate();
    }

    @Override
    public Set<Resource> searchResourcesByName(String searchString) throws SQLException, UnknownHostException {

        Set<Resource> resourceSet = new HashSet<Resource>();
        String getResourcesQuery = "SELECT * FROM " + RESOURCE_TABLE + " WHERE  resource_name LIKE '%" + searchString + "%'";
        Statement statement = getDatabaseConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(getResourcesQuery);

        while (resultSet.next()) {
            Resource resource = new Resource();
            resource.setResourceId(resultSet.getInt("resource_id"));
            resource.setName(resultSet.getString("resource_name"));
            resource.setResourceTypeId(resultSet.getInt("resource_type_id"));
            if (resultSet.getInt("availability") == 1) {
                resource.setAvailability(true);
            }
            resourceSet.add(resource);
        }
        return resourceSet;
    }

    @Override
    public Set<ResourcePreference> retrievePreferencesForResource(int resourceId) throws SQLException, UnknownHostException {
        Set<ResourcePreference> resourcePreferenceSet = new HashSet<ResourcePreference>();

        String getRequirementQuery = "SELECT * FROM " + RESOURCE_PREFERENCE_TABLE + " WHERE resource_id = ?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);
        preparedStatement.setInt(1, resourceId);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            ResourcePreference resourcePreference = new ResourcePreference();
            resourcePreference.setResourceId(resultSet.getInt("resource_id"));
            resourcePreference.setTaskId(resultSet.getInt("task_id"));
            resourcePreference.setPreference(resultSet.getInt("preference"));
            resourcePreferenceSet.add(resourcePreference);
        }
        return resourcePreferenceSet;
    }

    @Override
    public int updateResourceDetails(Resource resource, int previousEmployeeId) throws SQLException, UnknownHostException {
        String query = "UPDATE " + RESOURCE_TABLE + " set resource_name = ?, resource_id = ?, resource_type_id = ?  where resource_id = ?";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setString(1, resource.getName());
        statement.setInt(2, resource.getResourceId());
        statement.setInt(3, resource.getResourceTypeId());
        statement.setInt(4, previousEmployeeId);
        return statement.executeUpdate();
    }

    @Override
    public int saveResourceImage(int resourceId, File file) throws SQLException, UnknownHostException, FileNotFoundException {
        String query = "UPDATE " + RESOURCE_TABLE + " set Profile_Picture=? where resource_id=?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(query);
        preparedStatement.setInt(2, resourceId);
        preparedStatement.setBinaryStream(1, new FileInputStream(file), (int) (file.length()));
        return preparedStatement.executeUpdate();
    }

    // TODO: update and save both are done using the same function.better to rename the method as saveEmployeePreference
    @Override
    public int updateResourcePreference(ResourcePreference resourcePreference) throws SQLException, UnknownHostException {
        String query = "UPDATE " + RESOURCE_PREFERENCE_TABLE + " SET preference=? WHERE resource_id = ? AND task_id = ?";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, resourcePreference.getPreference());
        statement.setInt(2, resourcePreference.getResourceId());
        statement.setInt(3, resourcePreference.getTaskId());
        int result = statement.executeUpdate();
        if (result == 0) {
            int inserted = insertResourcePreferenceRecord(resourcePreference);
            return inserted;
        } else {
            return result;
        }
    }

    @Override
    public Task getTaskById(int taskId) throws SQLException, UnknownHostException {
        Task task = new Task();
        String getRequirementQuery = "SELECT * FROM " + TASK_TABLE + " WHERE task_id=?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);
        preparedStatement.setInt(1, taskId);
        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.first()) {
            task.setTaskId(resultSet.getInt("task_id"));
            task.setTaskName(resultSet.getString("name"));
        }
        return task;
    }

    @Override
    public TimeSlot getTimeSlotById(int id) throws SQLException, UnknownHostException {
        TimeSlot timeSlot = new TimeSlot();
        String query = "SELECT * FROM " + TIME_SLOT_TABLE + " WHERE time_slot_id = ?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(query);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.first()) {
            timeSlot.setId(resultSet.getInt("time_slot_id"));
            timeSlot.setDescription(resultSet.getString("name"));
        }
        return timeSlot;
    }

    @Override
    public int updateResourceCapability(ResourceCapability resourceCapability) throws SQLException, UnknownHostException {
        String query = "UPDATE " + RESOURCE_CAPABILITY_TABLE + " SET capability=? WHERE resource_id = ? AND task_id = ?";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, resourceCapability.getCapability());
        statement.setInt(2, resourceCapability.getResourceId());
        statement.setInt(3, resourceCapability.getTaskId());
        int result = statement.executeUpdate();
        if (result == 0) {
            return insertResourceCapability(resourceCapability);
        } else {
            return result;
        }
    }

    @Override
    public int updateManagerPreference(ManagerPreference managerPreference) throws SQLException, UnknownHostException {
        String query = "UPDATE " + MANAGER_PREFERENCE_TABLE + " SET preference=? WHERE resource_id = ? AND task_id = ?";
        PreparedStatement statement = getDatabaseConnection().prepareStatement(query);
        statement.setInt(1, managerPreference.getPreference());
        statement.setInt(2, managerPreference.getResourceId());
        statement.setInt(3, managerPreference.getTaskId());
        int result = statement.executeUpdate();
        if (result == 0) {
            return insertManagerPreference(managerPreference);
        } else {
            return result;
        }
    }

    @Override
    public Set<ResourceType> getResourceTypes() throws UnknownHostException, SQLException {
        Set<ResourceType> resourceTypeSet = new HashSet<ResourceType>();

        String getRequirementQuery = "SELECT * FROM " + RESOURCE_TYPE_TABLE;
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getRequirementQuery);

        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            ResourceType resourceType = new ResourceType();
            resourceType.setResourceTypeID(resultSet.getInt("resource_type_id"));
            resourceType.setName(resultSet.getString("name"));

        }
        return resourceTypeSet;
    }

    public Set<Resource> getResourcesByResourceType(int resourceTypeId) throws UnknownHostException, SQLException {
        Set<Resource> resourceSet = new HashSet<Resource>();
        String getResourcesQuery = "SELECT * FROM " + RESOURCE_TABLE + " WHERE availability = ? AND resource_type_id = ?";
        PreparedStatement preparedStatement = getDatabaseConnection().prepareStatement(getResourcesQuery);
        preparedStatement.setInt(1, 1);
        preparedStatement.setInt(2, resourceTypeId);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            Resource resource = new Resource();
            resource.setResourceId(resultSet.getInt("resource_id"));
            resource.setName(resultSet.getString("resource_name"));
            resource.setResourceTypeId(resultSet.getInt("resource_type_id"));
            if (resultSet.getInt("availability") == 1) {
                resource.setAvailability(true);
            }
            resourceSet.add(resource);
        }
        return resourceSet;
    }
}
